﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using fuzhu;
using System.Data.SqlClient;

namespace GLY_XieTong
{

    public class XieTong
    {
        

        // A,B 账连接字符串
        public string cConStrA { get; set; }
        public string cConStrB { get; set; }
        public string cZTA { get; set; }
        public string iYearA { get; set; }
        public string cZTB { get; set; }
        public string iYearB { get; set; }
        //中间库
         public string cConStr{ get; set; }
         public string cZJDATA { get; set; }  //中间库名
         public string cSourceCodeColA;
         public string cSourceCodeColB;
         public string cTargetCodeColA;
         public string cTargetCodeColB;

         public  XieTong(string C)
         {
             cConStr = C;
             cZJDATA = GetInterDataBase();
             
             string cDataA= GetDataA();
             cConStrA = cConStr.Replace(cZJDATA, cDataA);
             cZTA = cDataA.Substring(7, 3);
             iYearA = cDataA.Substring(11, 4);

             string cDataB = GetDataB();
             cConStrB = cConStr.Replace(cZJDATA, cDataB);
             cZTB = cDataB.Substring(7, 3);
             iYearB = cDataB.Substring(11, 4);
             
             cSourceCodeColA = GetSourceCodeColA();
             cSourceCodeColB = GetSourceCodeColB();
             cTargetCodeColA = GetTargetCodeColA();
             cTargetCodeColB = GetTargetCodeColB();
         }

         #region  基础数据获取
         /// <summary>
        /// 不用了
        /// </summary>
        /// <param name="csourceCodeColA"></param>
        /// <param name="csourceCodeColB"></param>
        /// <param name="ctargetCodeColA"></param>
        /// <param name="ctargetCodeColB"></param>
         public void setPara(string csourceCodeColA, string csourceCodeColB, string ctargetCodeColA, string ctargetCodeColB)
         {
             cSourceCodeColA = csourceCodeColA;
             cSourceCodeColB = csourceCodeColB;
             cTargetCodeColA = ctargetCodeColA;
             cTargetCodeColB = ctargetCodeColB;
         }
        /// <summary>
        /// 中间库
        /// </summary>
        /// <returns></returns>
         public string GetInterDataBase()
         {
             string sql = @"select DB_NAME()";
             return DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
         }
        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConStrA()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='7'";
           string cDataA = DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));
          return cConStr.Replace(cZJDATA, cDataA);        
        }
        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetDataA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='7'";
          return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));
             
        }

        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConStrB()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='8'";
           string cDataB = DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));
           return cConStr.Replace(cZJDATA, cDataB);
        
        }

        public string GetDataB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='8'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }
       /// <summary>
        /// 获取A的 来源单据 栏目名称
        /// </summary>
        /// <param name="cDateInter">中间库</param>
        /// <returns></returns>
        public string GetSourceCodeColA()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='9'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }

           /// <summary>
        /// 获取A的 去向单据 栏目名称
        /// </summary>
        /// <returns></returns>
        public string GetTargetCodeColA()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='10'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }
         
          /// <summary>
        /// 获取B的 来源单据 栏目名称
        /// </summary>
        /// <returns></returns>
        public string GetSourceCodeColB()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='11'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }

         /// <summary>
        /// 获取B的 去向单据 栏目名称
        /// </summary>
        /// <returns></returns>
        public string GetTargetCodeColB()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='12'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }

          /// <summary>
        /// 获取A的 表体来源单据id
        /// </summary>
        /// <returns></returns>
        public string GetSourceColsA()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='13'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }

        
          /// <summary>
        /// 获取A的 表体来源单据id
        /// </summary>
        /// <returns></returns>
        public string GetSourceColsB()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='14'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }
          
           /// <summary>
        /// 获取协同的 客户信息
        /// </summary>
        /// <returns></returns>
        public string GetcCusCode()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='1'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }

           /// <summary>
        /// 获取协同的 客户信息
        /// </summary>
        /// <returns></returns>
        public string GetcCusName()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='2'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }
          /// <summary>
        /// 获取协同的 客户信息
        /// </summary>
        /// <returns></returns>
        public string GetcCusAddress()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='3'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }
       
          /// <summary>
        /// 获取协同的 供应商信息
        /// </summary>
        /// <returns></returns>
        public string GetcVenCode()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='4'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));         
        
        }

          /// <summary>
        /// 获取协同的 仓库信息
        /// </summary>
        /// <returns></returns>
        public string GetcWhcode()
        {
           string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='6'";
           return  DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));   
        }
        #endregion

        #region 基础档案插入中间表
        /// <summary>
        /// 基础档案插入中间表： 存货分类、计量单位等单表档案，用时间戳判断更新
        /// cconstr: 来源单据字符串
        /// </summary>       
        public string DangAnINSZJB(string cLx,string cSourceConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK,ctimestamp,czjbfilter FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["czjbfilter"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //构造新增等sql语句
                    //1 检查是否有新增的档案
                    sql = string.Format(@"select top 1000  {0} from {1} a where not exists(select 1 from {2} b where a.{0}=b.{0} and (b.cstatus ='0' OR b.cstatus ='1')) {3}
                ", cPK, cSourceTable, cTarTable, cFilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cSourceConstr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            string cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);
                            //2、 insert栏目，批量插入中间表，逐条更新目标库


                            //获取id
                            int iId = GetInterTableID(cIdentity);
                            sql = string.Format(" insert into {0}({1}) select {2} from {3} a where {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cInvccode);

                            SqlParameter[] param2 = new SqlParameter[]{ 
                new SqlParameter("@id",iId),
                 new SqlParameter("@coperation","0")                                         
          };
                            int iRe = DbHelper2.ExecuteNonQuery(sql, param2, CommandType.Text, cSourceConstr);
                            if (iRe <1)
                            {

                                cError += cLx +":"+cInvccode + ":写入中间表执行不成功！"+ sql+"\r\n";
                            }
                                //存货档案及客户档案处理
                            else if (cLx == "存货档案INS中间表")
                            {
                                //存货档案插入子表
                              string cRe=  DangAnINSZJB_SUB("存货档案SUBINS中间表", iId, cInvccode, cSourceConstr);
                              if (cRe != "ok")
                              {
                                  cError +=  "存货档案SUBINS中间表:" + cInvccode + ":写入中间表执行不成功！"+ cRe+"\r\n";
                              }
                              cRe = DangAnINSZJB_SUB("存货档案BASINS中间表", iId, cInvccode, cSourceConstr);
                              if (cRe != "ok")
                              {
                                  cError +=  "存货档案BASINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                }
                            }
                            else if (cLx == "客户档案INS中间表")
                            {
                                //存货档案插入子表
                                string cRe = DangAnINSZJB_SUB("客户银行INS中间表", iId, cInvccode, cSourceConstr);
                                if (cRe != "ok")
                                {
                                    cError += "客户银行INS中间表:" + cInvccode + ":写入中间表执行不成功!" + cRe + "\r\n";
                                }
                                cRe = DangAnINSZJB_SUB("客户地址INS中间表", iId, cInvccode, cSourceConstr);
                                if (cRe != "ok")
                                {
                                    cError += "客户地址INS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                }
                            }
                        }

                    }
                  
                    {
                        //2 检查是否有更改的档案，时间戳不一样
                        //先把之前执行完成的更改成2
                        sql = string.Format(@"  UPDATE  {0} SET cstatus = 2  WHERE cstatus<> '2'  and ID NOT IN 
(SELECT MAX(ID)  FROM {0} where (cstatus ='0' OR cstatus ='1')  GROUP BY {1})  ", cTarTable, cPK);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);

                        //sql = string.Format("delete from {0}  where cstatus = 2 and DCREATEDATE<dateadd(month,-1,getdate())", cTarTable);
                        sql = string.Format("delete from {0}  where cstatus = 2 ", cTarTable);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);


                        sql = string.Format(@"select top 1000  {0} from {1} a where exists(select 1 from {2} b,(SELECT MAX(ID) ID,{0}  FROM {2} where (cstatus ='0' OR cstatus ='1')  GROUP BY {0}) c 
where a.{0}=b.{0} and b.id=c.id and convert(varchar(50),a.{3})<>b.{3} )
                ", cPK, cSourceTable, cTarTable, ctimestamp);
                        DataTable dtUps = DbHelper2.Execute(sql, cSourceConstr).Tables[0];

                        if (dtUps.Rows.Count > 0)
                        {

                            for (int i = 0; i < dtUps.Rows.Count; i++)
                            {

                                string cInvccode = DbHelper.GetDbString(dtUps.Rows[i][0]);
                                //2、 insert栏目，批量插入中间表，逐条更新目标库


                                int iId = GetInterTableID(cIdentity);

                                sql = string.Format(" insert into {0}({1}) select {2} from {3} a where {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cInvccode);

                                SqlParameter[] param2 = new SqlParameter[]{ 
                new SqlParameter("@id",iId),
                 new SqlParameter("@coperation","1")                                         
          };
                                int iRe = DbHelper2.ExecuteNonQuery(sql, param2, CommandType.Text, cSourceConstr);
                                if (iRe <1)
                                {

                                    cError += cInvccode + ":写入中间表执行不成功！\r\n";
                                }
                                else if (cLx == "存货档案INS中间表")
                                {
                                    //存货档案插入子表
                                    string cRe = DangAnINSZJB_SUB("存货档案SUBINS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "存货档案SUBINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                    cRe = DangAnINSZJB_SUB("存货档案BASINS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "存货档案BASINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                }
                                else if (cLx == "客户档案INS中间表")
                                {
                                    //存货档案插入子表
                                    string cRe = DangAnINSZJB_SUB("客户银行INS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "客户银行INS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                    cRe = DangAnINSZJB_SUB("客户地址INS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "客户地址INS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                }
                            }

                        }
                    }
                    if (cError != "")
                    {
                        return "error:" + cError;
                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:"+ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);
            
            }
        }
        #endregion
         #region 获得中间表的id
         /// <summary>
        /// 获得中间表的id
        /// </summary>
        /// <param name="cIdentity">表标识</param>
        /// <returns></returns>
        private int GetInterTableID(string cIdentity)
        {
            //获取id
            SqlParameter[] param = new SqlParameter[]{ 
                new SqlParameter("@cVouchType",cIdentity),
                 new SqlParameter("@iAmount",1),
                  new SqlParameter("@iFatherId",SqlDbType.Int),
                 new SqlParameter("@iChildId",SqlDbType.Int)                                         
          };
            param[2].Direction = ParameterDirection.Output;
            param[3].Direction = ParameterDirection.Output;
            DbHelper2.ExecuteNonQuery("sp_Inter_GetID", param, CommandType.StoredProcedure, cConStr);
            int iId = DbHelper.GetDbInt(param[2].Value);
            return iId;
        }

        /// <summary>
        /// 获得中间表的id
        /// </summary>
        /// <param name="cIdentity">表标识</param>
        /// <returns></returns>
        private int GetInterTableID(string cIdentity,int iAmount, out int iChildid)
        {
            //获取id
            SqlParameter[] param = new SqlParameter[]{ 
                new SqlParameter("@cVouchType",cIdentity),
                 new SqlParameter("@iAmount",iAmount),
                  new SqlParameter("@iFatherId",SqlDbType.Int),
                 new SqlParameter("@iChildId",SqlDbType.Int)                                         
          };
            param[2].Direction = ParameterDirection.Output;
            param[3].Direction = ParameterDirection.Output;
            DbHelper2.ExecuteNonQuery("sp_Inter_GetID", param, CommandType.StoredProcedure, cConStr);
            int iId = DbHelper.GetDbInt(param[2].Value);
            iChildid = DbHelper.GetDbInt(param[3].Value);
            return iId;
        }
         #endregion

        #region 中间表插入基础档案
        /// <summary>
        /// 基础档案插入中间表： 存货分类、计量单位等单表档案，用时间戳判断更新
        /// </summary>
        public string ZJBINSDangAn(string cLx,string cTarConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable =  DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    
                    //1 检查未更新的中间表档案，根据编码判断是否新增，不用operation
                    sql = string.Format(@"select {0},id from {1} a where  cstatus ='0' ", cPK, cSourceTable);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        string cInvccode = "";
                        int iId = 0;
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            try
                            {
                                cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);  //档案编码
                                iId = DbHelper.GetDbInt(dtIns.Rows[i][1]);   //中间表主键
                                //2、 查询目标库是否有, 没有写事务
                                sql = string.Format("select count(1) as cnt from {0} where {1}='{2}' ", cTarTable, cPK, cInvccode);
                                int iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cTarConstr));
                                if (iCnt == 0)
                                {
                                    //新增
                                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where id='{4}'", cTarTable, cColumn, cValue, cSourceTable, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe == 1)
                                    {
                                        //存货档案，执行子表的更新
                                        if (cLx == "中间表TO存货档案")
                                        {
                                            string cRe = ZJBINSDangAnSub("中间表TO存货档案SUB", iId,cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案SUB:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAnSub("中间表TO存货档案BAS", iId,cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案BAS:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                        }
                                        else if (cLx == "中间表TO客户档案")  // 客户档案                                     
                                        {
                                            string cRe = ZJBINSDangAn2("中间表TO客户银行", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户银行" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAn2("中间表TO客户地址", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户地址:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }

                                        }
                                        if (cError != "")
                                        {
                                            sql = string.Format("update {0} set cstatus = 99,cremark = '{2}' where id = '{1}'", cSourceTable, iId, cError);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                        else
                                        {
                                            sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}'", cSourceTable, iId);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                    }
                                    else
                                    {
                                        cError += cLx+":"+cInvccode + ":写入目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }
                                else
                                {
                                    //修改, a,b 不要搞错。 原表设置为a
                                    sql = string.Format("update {0} set {1} from {0} b,{4} a   where a.{2}=b.{2} and  a.{2}='{3}' and a.id ={5} ", cTarTable, cUpdateColunm, cPK, cInvccode, cSourceTable, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe >=1)
                                    {
                                        //存货档案，执行子表的更新
                                        if (cLx == "中间表TO存货档案")
                                        {
                                            string cRe = ZJBINSDangAnSub("中间表TO存货档案SUB", iId,cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案SUB:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAnSub("中间表TO存货档案BAS", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案BAS:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                        }
                                        else if (cLx == "中间表TO客户档案")  // 客户档案                                     
                                        {
                                            string cRe = ZJBINSDangAn2("中间表TO客户银行", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户银行" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAn2("中间表TO客户地址", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户地址:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }

                                        }
                                        if (cError != "")
                                        {
                                            sql = string.Format("update {0} set cstatus = 99,cremark = '{2}' where id = '{1}'", cSourceTable, iId, cError);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                        else
                                        {
                                            sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}'", cSourceTable, iId);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                    }
                                    else
                                    {
                                        cError += cLx+":"+cInvccode + ":更新目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }
                                

                            }
                            catch (Exception ex)
                            {
                                //WriteLog wr = new WriteLog();
                                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                                cError += ex.Message;
                                sql = string.Format("update {0} set cstatus = 99,cremark='{2}' where id = '{1}'", cSourceTable, iId, ex.Message);
                                DbHelper2.ExecuteNonQuery(sql, cConStr);
                                continue;
                            }
                        }
                    } 
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                if (cError!="")
                {
                    return cError;
                }
                else
                {
                    return "ok";
                }
               
            }
            catch (Exception ex)
            {
            
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }   
        #endregion

        #region 子表插入中间表
        /// <summary>
        /// 子表查询，id使用存货档案的id，ccode代表存货编码
        /// </summary>
        /// <param name="cLx">类型</param>
        /// <param name="ID">存货档案的id</param>
        /// <param name="cCode">存货编码</param>
        /// <returns></returns>
        public string DangAnINSZJB_SUB(string cLx, int iID,string cCode,string cSourceConstr)
        {
         try
            {
                
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK,ctimestamp FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);


                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));


                    //先删除之前的
                    sql = string.Format(" delete from  {0}  where {1}='{2}'", cTarTable, cPK, cCode);
                    DbHelper2.ExecuteNonQuery(sql, cConStr);
    
                    //插入新增档案
                       sql = string.Format(" insert into {0}({1}) select {2} from {3} a where {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cCode);
                   SqlParameter[] param2 = new SqlParameter[]{ 
                new SqlParameter("@id",iID),                                        
          };
                    int iRe = DbHelper2.ExecuteNonQuery(sql, param2, CommandType.Text, cSourceConstr);
                    if (iRe <1 && cLx.Contains("存货档案"))
                            {
                                cError += cLx +":"+cCode +",写入中间表执行不成功！\r\n";
                            }

                      
                }
                if (cError != "")
                {
                    return "error:" + cError;
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region 中间表子表插入基础档案
        /// <summary>
        /// 子表插入，根据主表的id进行插入
        /// </summary>
        public string ZJBINSDangAnSub(string cLx,int iId, string cTarConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //1 检查未更新的中间表档案，根据编码判断是否新增，不用operation
                    sql = string.Format(@"select {0},id from {1} a where  id ={2} ", cPK, cSourceTable,iId);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        string cInvccode = "";
                      
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            try
                            {
                                cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);  //档案编码
                                //2、 查询目标库是否有
                                sql = string.Format("select count(1) as cnt from {0} where {1}='{2}' ", cTarTable, cPK, cInvccode);
                                int iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cTarConstr));
                                if (iCnt == 0)
                                {
                                    //新增
                                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where id='{6}' and  {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cInvccode,iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe <0)
                                    {
                                        cError += cLx+":"+cInvccode + ":写入目标表执行不成功！\r\n";                                       
                                    }
                                }
                                else
                                {
                                    //修改, a,b 不要搞错。 原表设置为a
                                    sql = string.Format("update {0} set {1} from {0} b,{4} a   where a.{2}=b.{2} and  a.{2}='{3}' and a.id ={5} ", cTarTable, cUpdateColunm, cPK, cInvccode, cSourceTable, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe >0)
                                    {
                                        sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                    else
                                    {
                                        cError += cInvccode + ":更新目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }

                            }
                            catch (Exception ex)
                            {
                                //WriteLog wr = new WriteLog();
                                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                                sql = string.Format("update {0} set cstatus = 99,cremark='{2}' where id = '{1}'", cSourceTable, iId, ex.Message);
                                //DbHelper2.ExecuteNonQuery(sql, cConStr);
                                continue;
                            }

                        }

                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region 中间表子表插入基础档案，两个主键
        /// <summary>
        /// 子表插入，根据主表的id进行插入
        /// </summary>
        public string ZJBINSDangAn2(string cLx, int iId, string cTarConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK ,PKS FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //1 检查未更新的中间表档案，根据编码判断是否新增，不用operation
                    sql = string.Format(@"select {0},{1}, id from {2} a where  id ={3} ", cPK, cPKS, cSourceTable, iId);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        string cInvccode = "";
                        string cInvccode2 = "";
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            try
                            {
                                cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);  //档案编码
                                cInvccode2 = DbHelper.GetDbString(dtIns.Rows[i][1]);  //档案编码
                                //2、 查询目标库是否有
                              
                                sql = string.Format("select count(1) as cnt from {0} where {1}='{2}' AND {3}='{4}' ", cTarTable, cPK, cInvccode,cPKS,cInvccode2);
                                int iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cTarConstr));
                                if (iCnt == 0)
                                {
                                    //新增
                                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where id='{4}' AND {5}='{6}' AND {7}='{8}'  ", cTarTable, cColumn, cValue, cSourceTable, iId ,cPK, cInvccode,cPKS,cInvccode2);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe != 1)
                                    {
                                        cError += cLx + ":" + cInvccode + ":写入目标表执行不成功！\r\n";
                                    }
                                }
                                else
                                {
                                    //修改, a,b 不要搞错。 原表设置为a
                                    sql = string.Format("update {0} set {1} from {0} b,{4} a   where a.{2}=b.{2} and a.{6}=b.{6} and  a.{2}='{3}' and a.{6}='{7}' and a.id ={5} ", cTarTable, cUpdateColunm, cPK, cInvccode, cSourceTable, iId, cPKS, cInvccode2);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe>=1)
                                    {
                                        sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}' and {2}='{3}' AND {4}='{5}'", cSourceTable, iId, cPK, cInvccode, cPKS, cInvccode2);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                    else
                                    {
                                        cError += cInvccode + ":更新目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'  and {2}='{3}' AND {4}='{5}'", cSourceTable, iId,cPK, cInvccode, cPKS, cInvccode2);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }

                            }
                            catch (Exception ex)
                            {
                                //WriteLog wr = new WriteLog();
                                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                                sql = string.Format("update {0} set cstatus = 99,cremark='{2}' where id = '{1}'", cSourceTable, iId, ex.Message);
                                //DbHelper2.ExecuteNonQuery(sql, cConStr);
                                continue;
                            }

                        }

                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion
        
        #region 单据插入中间表
        /// <summary>
        /// /// <summary>
        /// 单据插入中间表，用单据id进行插入,  用时间戳判断更新
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        ///  <param name="cconstr">来源单据的连接字符串</param>
        /// <returns></returns>
   
        public string VoucherINSZJB(string cLx,string cfilter,string cconstrA,out  int idout,out  string ccode2 )
        {
            try
            {
                //1 是主表  2 是子表
                string cError = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 =  DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cFullTarTable1 = cZJDATA + ".DBO." + DbHelper.GetDbString(dt1.Rows[0]["ctable"]);  //全名，加上库名
                    string cFullTarTable2 = cZJDATA + ".DBO." + DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    
                    
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句


                    //2、 insert栏目，批量插入中间表，逐条更新目标库
                    //获取id 


                    //循环+事务.  要插入的表格，依次插入到中间表   子表autoid循环
                    int iAutoid = 0;
                    sql = string.Format("select b.{0} {1} {2}", cPKS, cFrom, cfilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cconstrA).Tables[0];
                    iId = GetInterTableID(cIdentity, dtIns.Rows.Count, out iChild);
                    iChild = iChild + 1 - dtIns.Rows.Count;  //子表id要重新计算初始值，返回的是最大值
                    //源表的连接事务
                    SqlTransaction tran = DbHelper2.BeginTrans(cconstrA);
                    try
                    {

                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            iAutoid = DbHelper.GetDbInt(dtIns.Rows[i][0]);
                            if (i == 0)
                            {
                                //插入主表
                                SqlParameter[] param = new SqlParameter[]{ 
                new SqlParameter("@id",iId)
                                  };
                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cFullTarTable1, cColumn1, cValue1, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, param, CommandType.Text, tran);
                            }
                            //插入子表
                            SqlParameter[] param2 = new SqlParameter[]{ 
                new SqlParameter("@id",iId),
                 new SqlParameter("@autoid",iChild+i)
                                  };
                            sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cFullTarTable2, cColumn2, cValue2, cFrom, cfilter, cPKS, iAutoid);
                            DbHelper2.ExecuteSqlWithTrans(sql, param2, CommandType.Text, tran);

                        }
                        DbHelper2.CommitTransAndCloseConnection(tran);
                    }
                    catch (Exception ex)
                    {
                        DbHelper2.RollbackAndCloseConnection(tran);
                        //WriteLog wr = new WriteLog();
                        //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                        cError = ex.Message;
                    }
                }
                    if (cError != "")
                    {
                        idout = 0;
                        ccode2 = "";
                        return "error:" + cError;
                    }
                    idout = iId;
                    ccode2 = "";
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion





        #region  中间表插入目标单据
        /// <summary>
        /// /// <summary>
        /// 中间表，生成目标单据
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string ZJBToVoucher(string cLx, int iSoucceID,string cconstrB, out  int idout, out  string ccode2)
        {
            try
            {
                //1 是主表  2 是子表
                string cError = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);             
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句


                    //2、 insert栏目，批量插入中间表，逐条更新目标库
                    //获取id 


                    //循环+事务.  要插入的表格，依次插入到中间表   子表autoid循环
                    int iAutoid = 0;
                    string cfilter = string.Format(" where a.id='{0}'", iSoucceID);
                    sql = string.Format("select b.{0} {1} {2}", cPKS, cFrom, cfilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cconstrB).Tables[0];
                  //获得目标库的account ，账套号
                   sql = " SELECT  dbo.fn_fd_getAccId()";
                   string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cconstrB));
                    //取U8的id,如果是max的取法不一样
                    if (cIdentity != "max")
                    {
                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cconstrB);
                        iId = iU8ID[0];
                        iChild = iU8ID[1];
                        iChild = iChild - dtIns.Rows.Count + 1;
                    }
                    else
                    {
                        sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPK, cTarTable1);
                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                        iId = iId + 1;
                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                        {
                            sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPKS, cTarTable2);
                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                            iChild = iId + 1;
                        }
                    }

                    //取参数,单据号、行条码、单据条码字段名称.cCodeName2 表体的单据号
                    string cIDname = "", cCodeName = "", cSysCodeName = "", cbSysCodeName = "", cCodeName2 = "";
                    string  cAutoIDname ="",cRowNoName="" ;
                    //第一行的id= iChild+iRowno-1；
                    int iRowno=1;  
                     List<SqlParameter> listParas1 = new List<SqlParameter>();                                 
                     //id
                     sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable1);
                     cIDname =DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql,cConStr));
                    if (!string.IsNullOrEmpty(cIDname))
                    {
                        listParas1.Add(new SqlParameter("@id", iId));  
                    }
                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable1);
                    cCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cCodeName))
                    {
                        listParas1.Add(new SqlParameter("@ccode", iId));
                    }
                    //cSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@csysbarcode'", cLx, cTarTable1);
                    cSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cSysCodeName))
                    {
                        listParas1.Add(new SqlParameter("@csysbarcode", iId));
                    }
                    SqlParameter[] paras1 = listParas1.ToArray();


                    //listParas2,  放到循环里去
                    
                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable2);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    
                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable2);
                    cCodeName2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                                   
                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    
                    //rowno
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@irowno'", cLx, cTarTable2);
                    cRowNoName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                                     
                    //cbSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@cbsysbarcode'", cLx, cTarTable2);
                    cbSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                   
                    //目标表的连接事务
                    SqlTransaction tran = DbHelper2.BeginTrans(cconstrB);
                    try
                    {
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            iRowno = i + 1;
                            iAutoid = DbHelper.GetDbInt(dtIns.Rows[i][0]);
                            if (i == 0)
                            {
                                //插入主表
                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable1, cColumn1, cValue1, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, paras1, CommandType.Text, tran);
                            }
                            //构造子表的参数
                            List<SqlParameter> listParas2 = new List<SqlParameter>();
                            if (!string.IsNullOrEmpty(cIDname))
                            {
                                listParas2.Add(new SqlParameter("@id", iId));
                            }
                            if (!string.IsNullOrEmpty(cCodeName2))
                            {
                                listParas2.Add(new SqlParameter("@ccode", iId));
                            }
                            if (!string.IsNullOrEmpty(cAutoIDname))
                            {
                                listParas2.Add(new SqlParameter("@autoid", iChild + iRowno - 1));
                            }
                            if (!string.IsNullOrEmpty(cRowNoName))
                            {
                                listParas2.Add(new SqlParameter("@irowno", iRowno));
                            }
                            if (!string.IsNullOrEmpty(cbSysCodeName))
                            {
                                listParas2.Add(new SqlParameter("@cbsysbarcode", iId));
                            }
                            SqlParameter[] paras2 = listParas2.ToArray();

                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable2, cColumn2, cValue2, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, paras2, CommandType.Text, tran);
                         
                        }
                        string cRe = GuanLian(cLx, iId, iSoucceID, tran);
                        if (cRe != "ok")
                        {
                            throw new Exception("关联出错:" + cRe);
                        }

                        GenVoucherNum genVoucherNum = new GenVoucherNum(cCardnumber, iId, cconstrB);
                        ccode2 = genVoucherNum.GetNum(tran);

                        int iChongFu = 10;
                        sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                        int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        while (iCnt > 0 && iChongFu > 0)
                        {
                            //更新单据号码  ,10次还是取不到，报错                        
                            ccode2 = genVoucherNum.GetNum(tran);
                            iChongFu--;
                            sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                            iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        }
                        if (iChongFu == 0)
                        {
                            throw new Exception("获取单据编码错误！");
                        }

                        //更新单据号
                        sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cCodeName,  ccode2 , cIDname, iId);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);

                        //更新单据号,表体
                        if (!string.IsNullOrEmpty(cCodeName2))
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable2, cCodeName, ccode2, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        if (!string.IsNullOrEmpty(cSysCodeName) && cSysCodeName != "")
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }
                        if (!string.IsNullOrEmpty(cbSysCodeName) && cbSysCodeName != "")
                        {
                            if (!string.IsNullOrEmpty(cRowNoName) && cRowNoName != "")
                                sql = string.Format(" update {0} set {1}= '{2}'+convert(varchar(10),{3}) where  {4}='{5}'", cTarTable2, cbSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|" ,cRowNoName, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        DbHelper2.CommitTransAndCloseConnection(tran);


                    }
                    catch (Exception ex)
                    {
                        DbHelper2.RollbackAndCloseConnection(tran);
                        //WriteLog wr = new WriteLog();
                        //wr.WriteLogs(cLx + "单据生成：" + ex.Message);
                        idout = 0;
                        ccode2 = "";
                        return "error:" + ex.Message;
                    }
      
                }
               
                idout = iId;
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion
        
        #region  中间表更新到目标单据
        /// <summary>
        /// /// <summary>
        /// 单据更新中间表，表头更新，表体有 增加、删除、更新..暂时放弃
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string ZJBUPDATEVoucher(string cLx,int iID,int iSoucceID, out  int idout, out  string ccode2)
        {
            try
            {
                //iID 是主表id 
                string cError = "";
                int iChild = 0;   //插入表的子表id
               
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);

                    //构造更新sql语句
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''  and bupdate=1 and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cUpdate1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1 and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cUpdate2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                }
                idout = 0;
                ccode2 = "";
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion
        
        #region  单据插入目标单据
        /// <summary>
        /// /// <summary>
        /// U8单据，生成目标单据
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string VoucherToVoucher(string cLx, int iSouceId,string cfilter, string cconstrB, out  int idout, out  string ccode2)
        {
            try
            {
                //1 是主表  2 是子表
                string cError = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句
                    
                    //2、 insert栏目，批量插入中间表，逐条更新目标库
                    //获取id 
                    
                    //循环+事务.  要插入的表格，依次插入到中间表   子表autoid循环
                    int iAutoid = 0;
                    //string cfilter = string.Format(" where a.id='{0}'", iSoucceID);
                    sql = string.Format("select b.{0} {1} {2}", cPKS, cFrom, cfilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cconstrB).Tables[0];
                    //获得目标库的account ，账套号
                    sql = " SELECT  dbo.fn_fd_getAccId()";
                    string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cconstrB));
                    //取U8的id,如果是max的取法不一样
                    if (cIdentity != "max")
                    {
                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cconstrB);
                        iId = iU8ID[0];
                        iChild = iU8ID[1];
                        iChild = iChild - dtIns.Rows.Count + 1;
                    }
                    else
                    {
                        sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPK, cTarTable1);
                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                        iId = iId + 1;
                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                        {
                            sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPKS, cTarTable2);
                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                            iChild = iId + 1;
                        }
                    }

                    //取参数,单据号、行条码、单据条码字段名称.cCodeName2 表体的单据号
                    string cIDname = "", cCodeName = "", cSysCodeName = "", cbSysCodeName = "", cCodeName2 = "";
                    string cAutoIDname = "", cRowNoName = "";
                    //第一行的id= iChild+iRowno-1；
                    int iRowno = 1;
                    List<SqlParameter> listParas1 = new List<SqlParameter>();
                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable1);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cIDname))
                    {
                        listParas1.Add(new SqlParameter("@id", iId));
                    }
                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable1);
                    cCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cCodeName))
                    {
                        listParas1.Add(new SqlParameter("@ccode", iId));
                    }
                    //cSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@csysbarcode'", cLx, cTarTable1);
                    cSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cSysCodeName))
                    {
                        listParas1.Add(new SqlParameter("@csysbarcode", iId));
                    }
                    SqlParameter[] paras1 = listParas1.ToArray();


                    //listParas2,  放到循环里去天津

                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable2);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable2);
                    cCodeName2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //rowno
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@irowno'", cLx, cTarTable2);
                    cRowNoName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cbSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@cbsysbarcode'", cLx, cTarTable2);
                    cbSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //目标表的连接事务
                    SqlTransaction tran = DbHelper2.BeginTrans(cconstrB);
                    try
                    {
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            iRowno = i + 1;
                            iAutoid = DbHelper.GetDbInt(dtIns.Rows[i][0]);
                            if (i == 0)
                            {
                                //插入主表
                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable1, cColumn1, cValue1, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, paras1, CommandType.Text, tran);
                            }
                            //构造子表的参数
                            List<SqlParameter> listParas2 = new List<SqlParameter>();
                            if (!string.IsNullOrEmpty(cIDname))
                            {
                                listParas2.Add(new SqlParameter("@id", iId));
                            }
                            if (!string.IsNullOrEmpty(cCodeName2))
                            {
                                listParas2.Add(new SqlParameter("@ccode", iId));
                            }
                            if (!string.IsNullOrEmpty(cAutoIDname))
                            {
                                listParas2.Add(new SqlParameter("@autoid", iChild + iRowno - 1));
                            }
                            if (!string.IsNullOrEmpty(cRowNoName))
                            {
                                listParas2.Add(new SqlParameter("@irowno", iRowno));
                            }
                            if (!string.IsNullOrEmpty(cbSysCodeName))
                            {
                                listParas2.Add(new SqlParameter("@cbsysbarcode", iId));
                            }
                            SqlParameter[] paras2 = listParas2.ToArray();

                            sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable2, cColumn2, cValue2, cFrom, cfilter, cPKS, iAutoid);
                            DbHelper2.ExecuteSqlWithTrans(sql, paras2, CommandType.Text, tran);

                        }
                        string cRe = GuanLian(cLx, iId, iSouceId, tran);
                        if (cRe != "ok")
                        {
                            throw new Exception("关联出错:" + cRe);
                        }

                        GenVoucherNum genVoucherNum = new GenVoucherNum(cCardnumber, iId, cconstrB);
                        ccode2 = genVoucherNum.GetNum(tran);

                        int iChongFu = 10;
                        sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                        int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        while (iCnt > 0 && iChongFu > 0)
                        {
                            //更新单据号码  ,10次还是取不到，报错                        
                            ccode2 = genVoucherNum.GetNum(tran);
                            iChongFu--;
                            sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                            iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        }
                        if (iChongFu == 0)
                        {
                            throw new Exception("获取单据编码错误！");
                        }

                        //更新单据号
                        sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cCodeName, ccode2, cIDname, iId);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);

                        //更新单据号,表体
                        if (!string.IsNullOrEmpty(cCodeName2))
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable2, cCodeName, ccode2, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        if (!string.IsNullOrEmpty(cSysCodeName) && cSysCodeName != "")
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }
                        if (!string.IsNullOrEmpty(cbSysCodeName) && cbSysCodeName != "")
                        {
                            if (!string.IsNullOrEmpty(cRowNoName) && cRowNoName != "")
                                sql = string.Format(" update {0} set {1}= '{2}'+convert(varchar(10),{3}) where  {4}='{5}'", cTarTable2, cbSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cRowNoName, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        DbHelper2.CommitTransAndCloseConnection(tran);


                    }
                    catch (Exception ex)
                    {
                        DbHelper2.RollbackAndCloseConnection(tran);
                        //WriteLog wr = new WriteLog();
                        //wr.WriteLogs(cLx + "单据生成：" + ex.Message);
                        idout = 0;
                        ccode2 = "";
                        return "error:" + ex.Message;
                    }

                }

                idout = iId;
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region  生单之后的处理动作，回写关联表信息
        /// <summary>
        /// 生单之后的处理动作，回写关联表信息
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <param name="iID">生成单据主键</param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public string GuanLian(string cLx, int iID, int iSoucceID, SqlTransaction tran)
        {
            string sql = "";
            string cSourceCode = "";
            switch (cLx)
            {
                case "销售中间表TO销售订单":

                    sql = string.Format(@" select CPOID from zdy_inter_somain where id = {0}", iSoucceID);
                    cSourceCode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  INSERT INTO so_somain_extradefine(id,{0}) 
                  values('{1}','{2}')", cSourceCodeColB, iID, "数字采购订单-" + cSourceCode);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    sql = string.Format(@" insert into  SO_SODetails_extradefine(iSOsID)
select isosid from  SO_SODetails a where not exists(select 1 from  SO_SODetails_extradefine b where a.iSOsID=b.iSOsID)
and a.id = {0}
", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;
                case "发货中间表TO发货单":

                    sql = string.Format(@" select cdlcode from zdy_inter_dispatchlist where id = {0}", iSoucceID);
                    cSourceCode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  INSERT INTO dispatchlist_extradefine(dlid,{0}) 
                  values('{1}','{2}')", cSourceCodeColB, iID, "数字发货单-" + cSourceCode);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);


                    sql = string.Format(@"  update a set bReturnFlag =1 from DispatchList a,DispatchLists b where a.dlid = b.dlid 
and b.iQuantity<0 and a.bReturnFlag=0  and a.dlid ={0}",  iID );
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    //更新销售订单的发货数量，发货金额
                    sql = string.Format(@" UPDATE a SET iFHQuantity=isnull(iFHQuantity,0)+b.sl,a.iFHMoney=isnull(a.iFHMoney,0)+b.je FROM dbo.SO_SODetails a
INNER JOIN (SELECT iSOsID,SUM(iQuantity) sl,SUM(isum) je FROM dbo.DispatchLists WHERE dlid ={0} GROUP BY iSOsID) b
ON a.isosid = b.iSOsID", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    //更新业务员
                    sql = string.Format(@" update a set cdepcode=b.cdepcode, a.cPersonCode=b.cPersonCode 
from DispatchList a,so_somain b where a.csocode  = b.csocode and a.dlid ={0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                 

                    sql = string.Format(@"insert into SCM_entryledgerbuffer    
 (Subject,iNum,iQuantity,cWhCode,cInvCode,ItemId,    
  cVMIVenCode,cBatch,    
  DocumentType,DocumentId,DocumentDId,DemandPlanType,    
  DemandPlanDId,cFree1,cFree2,cFree3,cFree4,cFree5,    
  cFree6,cFree7,cFree8,cFree9,cFree10,dMDate,dVDate,    
  imassunit,imassday,cBusType,cSource,ddate,iBillNum,iBillQuantity,cExpirationdate,dExpirationdate,iExpiratDateCalcu)    
 select   '0301' 
  as Subject,      
b.inum*(-1) as inum,    
 b.iquantity*(-1)  as iQuantity, 
  b.cWhCode,b.cInvCode,0 as ItemId,    
  isnull(b.cVMIVenCode,'') as cVMIVenCode ,isnull(b.cBatch,'') as cBatch,    
  a.cvouchtype as DocumentType,a.dlid as DocumentId,b.autoid as DocumentDId,    
0 as DemandPlanType,    
  0 as DemandPlanDId,  
  isnull(b.cFree1,'') as cfree1,    
  isnull(b.cFree2,'') as cfree2,isnull(b.cFree3,'') as cfree3,isnull(b.cFree4,'') as cfree4,    
  isnull(b.cFree5,'') as cfree5,isnull(b.cFree6,'') as cfree6,isnull(b.cFree7,'') as cfree7,    
  isnull(b.cFree8,'') as cfree8,isnull(b.cFree9,'') as cfree9,isnull(b.cFree10,'') as cfree10,    
  b.dMDate as dMDate,b.dVDate,    
  b.cmassunit as imassunit,b.imassdate as imassday,a.cBusType,'销售订单',a.ddate,    
 0 AS iBillNum,    
  0 as iBillQuantity ,  
  b.cExpirationdate,b.dExpirationdate ,b.iExpiratDateCalcu  
  from dbo.DispatchList a with (nolock) inner join dbo.DispatchLists b with (nolock) on a.dlid=b.dlid     
  left join inventory i on b.cinvcode=i.cinvcode     
 where a.dlid ={0}   ", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)   
insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where Item.cInvCode=a.cInvCode and Item.cfree1=a.cfree1 
 and Item.cfree2=a.cfree2 and Item.cfree3=a.cfree3 and Item.cfree4=a.cfree4 and Item.cfree5=a.cfree5 
 and Item.cfree6=a.cfree6 and Item.cfree7=a.cfree7 and Item.cfree8=a.cfree8 and Item.cfree9=a.cfree9 and Item.cfree10=a.cfree10  )  
                    exec dbo.Usp_SCM_CommitEntryLedger @transactionid  
                    exec IA_SP_WriteUnAccountVouchForSA  {0},N'05'", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;
               
                case "采购入库中间表TO采购入库单":
                    #region 入库单回写
                    sql = string.Format(@" select ccode from zdy_inter_rdrecord01 where id = {0}", iSoucceID);
                    cSourceCode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@" INSERT INTO rdrecord01_extradefine(id,{0}) 
                  values('{1}','{2}')", cSourceCodeColA, iID, "激光销售出库单-" + cSourceCode);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                   
                //更新采购入库数量
                    sql = string.Format(@"  UPDATE a SET ireceivedqty=ISNULL(ireceivedqty,0)+b.sl FROM  PO_Podetails a,(SELECT iposid,SUM(iquantity) sl FROM rdrecords01 WHERE id  = {0} GROUP BY iPOsID) b
WHERE a.id = b.iPOsID", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    
                               
sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'01',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
update rdrecord01 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
exec IA_SP_WriteUnAccountVouchForST  {0},N'01'", iID);

 DbHelper2.ExecuteSqlWithTrans(sql, tran);

                #endregion
                    break;
                case "采购入库单TO销售出库单":

                    sql = string.Format(@" select ccode from rdrecord01 where id = {0}", iSoucceID);
                    cSourceCode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  INSERT INTO rdrecord32_extradefine(id,{0}) 
                  values('{1}','{2}')", cSourceCodeColB, iID, "数字采购入库单-" + cSourceCode);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                   
                  

                    //更新发货单累计出库数量
                    sql = string.Format(@" 
update so_sodetails set foutquantity =  isnull(a.fOutQuantity,0)+b.iqty from so_sodetails a,  dbo.DispatchLists c,(select  b.iDLsID,SUM(isnull(b.iQuantity,0)) iqty from   dbo.rdrecord32 a,rdrecords32 b WHERE a.id  =b.id AND a.id ={0} group by iDLsID) b where c.iDLsID = b.idlsid AND a.iSOsID = c.iSOsID
--更新发货单
update DispatchLists set fOutQuantity = ISNULL(fOutQuantity,0)+b.iqty 
FROM DispatchLists a,(select  b.iDLsID,SUM(isnull(b.iQuantity,0)) iqty from   dbo.rdrecord32 a,rdrecords32 b WHERE a.id  =b.id AND a.id ={0} group by iDLsID) b where a.iDLsID = b.idlsid

UPDATE DispatchList SET cSaleOut ='ST' FROM DispatchList A,rdrecord32 B WHERE A.dlid = B.cdlcode
AND b.id ={0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    //更新库存

                    sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'32',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid

update rdrecord32 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}

exec IA_SP_WriteUnAccountVouchForST32 {0},N'32','销售出库单','分期收款' 
", iID);
                    break;
                case "采购发票中间表TO采购发票":

                    sql = string.Format(@" select cpbvcode from ZDY_INTER_PURBILLVOUCH where id = {0}", iSoucceID);
                    cSourceCode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  INSERT INTO PURBILLVOUCH_extradefine(PBVid,{0}) 
                  values('{1}','{2}')", cSourceCodeColA, iID, "激光销售发票-" + cSourceCode);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    //更新采购发票累计到票数量

                   
   sql = string.Format(@" UPDATE a SET  iSumBillQuantity  = ISNULL(iSumBillQuantity,0)+sl  FROM   rdrecords01 a,
(SELECT rdsid,SUM(iPBVquantity)  sl  FROM  purbillvouchs where pbvid = {0}   GROUP BY  rdsid)  b
WHERE a.AutoID = b.rdsid",iID);
                      DbHelper2.ExecuteSqlWithTrans(sql, tran);
   sql = string.Format(@" UPDATE a SET  iInvQTY  = ISNULL(iInvQTY,0)+sl,iInvMoney=isnull(iInvMoney,0)+b.imoney  FROM   PO_Podetails a,
(SELECT b.iposid,SUM(a.iPBVquantity)  sl,sum(a.imoney) imoney  FROM  purbillvouchs a,rdrecords01  b where  pbvid = {0} and a.rdsid  = b.AutoID   GROUP BY  b.iposid)  b
WHERE a.ID = b.iposid", iID);
                      DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    break;
                default:
                    break;

                  
            }
            return "ok";
        }

        #endregion


        #region  插入需要转换的业务单据
        /// <summary>
        ///业务单据同步,类型用 插入中间表的类型
        /// </summary>
        public string UpdateVouch(string cLx, string cFilterOri)
        {
            try
            {
              string cError = "";
                //查询同步的数据表及主键


                //1 是主表  2 是子表
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cSourceTables = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //这两个中间表保留着，用于获取更新字段。 （主要是之前的插入部分不再更改，中间表用于插入）
                    string cZJBTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cZJBTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    //临时表
                    string cTmpTable = "#TMP" + cSourceTable;  //使用临时表
                                                               //string cTmpTables = "#TMP" + DbHelper.GetDbString(dt1.Rows[0]["ctables"]);


                    //临时写,目标表

                    string cTarCode = ""; //协同目标表的单据号
                    int iTarId = 0; //协同目标表的主键
                    if (cLx == "采购订单INS销售中间表")
                    {
                        sql = string.Format("select  right(chdefine5,len(chdefine5) - charindex('-',chdefine5) ) from po_pomain_extradefine a where 1=1 {0}", cFilterOri);
                        cTarCode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStrA));
                        sql = string.Format("select  id from so_somain where csocode='{0}' ", cTarCode);
                        iTarId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                        if (iTarId == 0)
                        {
                            cError = "协同单据不存在，请重新协同";
                            return cError;
                        }

                        string cIdentity = "Somain";
                        string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                        string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                        string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);

                        //表头只有更新，没有插入
                        //构造表头更新sql语句
                        sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+replace(replace(cdefaultvalue,'a.','Tmp.'),'b.','Tmp.') from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''  and bupdate=1 and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cZJBTable1);
                        string cUpdate1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                        sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+replace(replace(cdefaultvalue,'a.','Tmp.'),'b.','Tmp.') from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1 and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", "销售中间表TO销售订单", "so_sodetails");
                        string cUpdate2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                        //两个字段错误
                        cUpdate2 = cUpdate2.Replace("CBMEMO=", "CMEMO=").Replace("CBDEFINE22=", "CDEFINE22=");
                        cUpdate2 = cUpdate2.Replace("Tmp.dpredate", "Tmp.dArriveDate").Replace("Tmp.iTaxUnitPrice", "Tmp.iTaxPrice");
                        
                        //插入表体的字段                   
                        sql = string.Format(@" SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", "销售中间表TO销售订单", "so_sodetails");
                        string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                        sql = string.Format(@" SELECT STUFF((select ','+replace(replace(cdefaultvalue,'a.',''),'b.','') from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", "销售中间表TO销售订单", "so_sodetails");
                        string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                        cValue2 = cValue2.Replace("CBMEMO", "CMEMO").Replace("CBDEFINE22", "CDEFINE22").Replace("IPOIDS=", "ID");
                        cValue2 = cValue2.Replace("dpredate", "dArriveDate").Replace("iTaxUnitPrice", "iTaxPrice");
                        //建立临时表的栏目
                        sql = string.Format(@" SELECT STUFF((select distinct ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  (cdefaultvalue like 'a.%' or cdefaultvalue like 'b.%' ) FOR XML PATH('')),1,1,'')", cLx, cZJBTable2);
                        string cCreateColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                        //关联关系


                        //插入到目标库

                        var srcCreatetempsql = string.Format("select {2} into {0} {1} where 1=2   ", cTmpTable, cFrom, cCreateColumn);
                        //获取源数据
                        var tarSelectsql = string.Format(@"select {1}  {0} where 1=1 {2}", cFrom, cCreateColumn, cFilterOri);
                        DataTable tardt = DbHelper2.ExecuteTable(tarSelectsql, cConStrA);


                        //查询源表和目标表的差异，insert表， delete 表和更新表
                        using (SqlConnection conn = new SqlConnection(cConStrB))
                        {
                            using (SqlCommand command = new SqlCommand("", conn))
                            {
                                try
                                {
                                    conn.Open();
                                    //创建临时表
                                    command.CommandText = srcCreatetempsql;
                                    command.ExecuteNonQuery();
                                    //插入源表数据
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = cTmpTable;
                                        bulkcopy.WriteToServer(tardt);
                                        bulkcopy.Close();
                                    }
                                    //表头的更新
                                    command.CommandText = string.Format(@" update b set {0} from {1} Tmp,so_somain b where b.id = {2}", cUpdate1, cTmpTable, iTarId);
                                    command.ExecuteNonQuery();

                                    //表体的删除
                                    command.CommandText = string.Format(@" delete  from  b from  so_sodetails b
left join {0} Tmp  on  b.cDefine35=Tmp.id where b.id = {1} and Tmp.id is null  ", cTmpTable, iTarId);
                                    command.ExecuteNonQuery();
                                   
                                    
                                 
                                    //表体的更新
                                    command.CommandText = string.Format(@" update b set {0} from {1} Tmp,so_sodetails b where b.id = {2} and  b.cDefine35=Tmp.id", cUpdate2, cTmpTable, iTarId);
                                    command.ExecuteNonQuery();

                                    //表体的新增
                                    command.CommandText = string.Format(@"select Tmp.*,0 as idpks,0 as idpk,row_number () over (order by Tmp.id) as rowid  into #TmpIns from {0} Tmp 
left join so_sodetails b on  b.cDefine35 = Tmp.id and b.id = {1}
                                where  b.isosid is null select @@rowcount", cTmpTable, iTarId);
                                    //查询新增行数
                                    int iCnt = DbHelper.GetDbInt(command.ExecuteScalar());


                                    if (iCnt != 0)
                                    {
                                        sql = " SELECT  dbo.fn_fd_getAccId()";
                                        string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStrB));
                                        //取U8的id,如果是max的取法不一样
                                        if (cIdentity != "max")
                                        {
                                            int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, iCnt, cConStrB);
                                            iId = iU8ID[0];
                                            iChild = iU8ID[1];
                                            iChild = iChild - iCnt + 1;
                                        }
                                   
                                        command.CommandText = string.Format(@" update Tmp set idpk={0},idpks=rowid+{1} from #TmpIns Tmp", iTarId, iChild);
                                        command.ExecuteNonQuery();
                                        cValue2 = cValue2.Replace("@id", "idpk").Replace("@autoid", "idpks");
                                        cValue2 = cValue2.Replace("@ccode", "idpk").Replace("@cbsysbarcode", "idpk").Replace("IPOIDS","id").Replace("@irowno", "ivouchrowno");

                                    

                                        command.CommandText = string.Format(" insert into so_sodetails({0}) select {1} from  #TmpIns  ", cColumn2, cValue2);                                  
                                        command.ExecuteNonQuery();

                                        command.CommandText = string.Format(" update b set csocode=a.csocode,cbSysBarCode='||SA17|'+a.csocode+'|'+convert(varchar(10),b.irowno)  from so_somain a,so_sodetails b where a.id= b.id and a.id = {0} ", iTarId);

                                        command.ExecuteNonQuery();

                                    }
                                    return "";
                                }
                                catch (Exception ex)
                                {
                                    cError = ex.Message;
                                    throw new Exception(string.Format("error:{0}", ex.Message));
                                }
                                finally
                                {
                                    conn.Close();
                                }
                            }

                        }

                    }
                    else
                    {
                        return "只支持采购订单变更协同完成！";
                    }
                    //else if (cLx == "发货单INS发货中间表")
                    //{
                    //    cTarTable = "DISPATCHLIST";
                    //    cTarTables = "DISPATCHLISTS";
                    //}
                    //else if (cLx == "销售出库单INS采购入库中间表")
                    //{
                    //    cTarTable = "RDRECORD01";
                    //    cTarTables = "RDRECORDS01";
                    //}
                    //else if (cLx == "销售发票INS采购发票中间表")
                    //{
                    //    cTarTable = "PURBILLVOUCH";
                    //    cTarTables = "PURBILLVOUCHS";
                    //}
                }
                return cError;
            }
            catch (Exception ex)
            {
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion
    }
}
